Programming Microsoft Visual Basic .NET/Programming Microsoft Visual Basic .NET (Microsoft Press)(X08-78517)(2002).bin/setup/vbnet/23 web forms and controls/databinding/pagingform.aspx.vb

Text File | 2002-03-17 | 7.1 KB | 185 lines

Imports System.Data.OleDb

Public Class PagingForm
    Inherits System.Web.UI.Page

    Protected WithEvents dgrTitles As System.Web.UI.WebControls.DataGrid

#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

    End Sub

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub
    Protected WithEvents Panel1 As System.Web.UI.WebControls.Panel
    Protected WithEvents btnFirst As System.Web.UI.WebControls.Button
    Protected WithEvents btnPrevious As System.Web.UI.WebControls.Button
    Protected WithEvents btnNext As System.Web.UI.WebControls.Button
    Protected WithEvents btnLast As System.Web.UI.WebControls.Button
    Protected WithEvents txtPage As System.Web.UI.WebControls.TextBox
    Protected WithEvents btnGo As System.Web.UI.WebControls.Button

#End Region

    ' Change this constant to enforce a different paging technique
    '     0=default buttons, 1=custom buttons, 2=custom paging
#Const PAGINGSTYLE = 2

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        If Not Me.IsPostBack Then
            ' Show the first page.
            ShowPage(0)
        End If
    End Sub

#If PAGINGSTYLE = 0 Then
    ' This event is used only when using default navigation controls.
    Private Sub dgrTitles_PageIndexChanged(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridPageChangedEventArgs) Handles dgrTitles.PageIndexChanged
        dgrTitles.CurrentPageIndex = e.NewPageIndex
        BindDataGrid()
    End Sub
#End If

    ' react to a click on a navigational button

    Private Sub ButtonClick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click, btnPrevious.Click, btnNext.Click, btnLast.Click, btnGo.Click
        Dim pageNum As Integer = dgrTitles.CurrentPageIndex

        Select Case DirectCast(sender, Button).CommandName.ToLower
            Case "first"
                pageNum = 0
            Case "prev"
                pageNum -= 1
            Case "next"
                pageNum += 1
            Case "last"
                pageNum = dgrTitles.PageCount - 1
            Case "go"
                Try
                    ' Visible page number is 1-based.
                    pageNum = CInt(txtPage.Text) - 1
                Catch
                    Exit Sub
                End Try
        End Select

        ' Show this page.
        ShowPage(pageNum)
    End Sub

    ' Display a page.
    Sub ShowPage(ByVal pageNum As Integer)
        ' Keep the page number in valid range.
        pageNum = Math.Max(0, Math.Min(pageNum, dgrTitles.PageCount - 1))
        ' Enforce the new page number .
        dgrTitles.CurrentPageIndex = pageNum

        ' Bind the control.
        BindDataGrid()

        ' Update button state.
        btnFirst.Enabled = (pageNum > 0)
        btnPrevious.Enabled = (pageNum > 0)
        btnNext.Enabled = (pageNum < dgrTitles.PageCount - 1)
        btnLast.Enabled = (pageNum < dgrTitles.PageCount - 1)
        ' Display the current page number (1-based).
        txtPage.Text = (pageNum + 1).ToString
    End Sub

#If PAGINGSTYLE <> 2 Then

    ' Bind the DataGrid control
    ' When doing default paging, you load all the data in a DataTable
    ' and let the DataGrid do the pagination automatically

    Sub BindDataGrid()
        ' Retrieve the DataSet from the session variable.
        Dim ds As DataSet = directcast(Session("DataSet"), DataSet)

        ' Read data from database if this is the first time we do it.
        If ds Is Nothing Then
            Dim cn As New OleDbConnection(BiblioConnString)
            cn.Open()
            ' Read data from Titles table + Name of publisher.
            Dim sql As String = "SELECT Titles.*, Publishers.Name As PubName FROM Titles INNER JOIN Publishers ON Titles.PubId=Publishers.PubId"
            Dim da As New OleDbDataAdapter(sql, cn)
            ds = New DataSet()
            da.Fill(ds, "Titles")
            cn.Close()
            ' Store the DataSet in a Session variable.
            Session("DataSet") = ds
        End If

        ' Get a DataView object sorted on the required sort expression.
        Dim dv As DataView = ds.Tables("Titles").DefaultView
        dv.Sort = dgrTitles.Attributes("SortExpr")
        dgrTitles.DataSource = dv

        ' Do the data binding.
        dgrTitles.DataBind()
    End Sub

#Else

    ' when doing custom paging you must load only the data
    ' strictly needed for the current page

    Sub BindDataGrid()
        ' Open the connection.
        Dim cn As New OleDbConnection(BiblioConnString)
        cn.Open()

        ' If this isn't a post back, let's initialize the VirtualItemCount with number of records.
        If Not Me.IsPostBack Then
            Dim cmd As New OleDbCommand("SELECT COUNT(*) FROM Titles", cn)
            dgrTitles.VirtualItemCount = CInt(cmd.ExecuteScalar)
        End If

        ' Prepare to read from Titles table + Name of publisher.
        Dim sql As String = "SELECT Titles.*, Publishers.Name As PubName FROM Titles INNER JOIN Publishers ON Titles.PubId=Publishers.PubId"
        ' Append sort expression, if there is one.
        Dim sortExpr As String = dgrTitles.Attributes("SortExpr")
        If Not (sortExpr Is Nothing) AndAlso sortExpr.ToString <> "" Then
            If sortExpr.StartsWith("PubName") Then
                sortExpr = "Publishers.Name" & sortExpr.Substring(7)
            End If
            sql &= " ORDER BY " & sortExpr
        End If
        ' Fill a DataTable only with data for the current page.
        Dim ds As New DataSet()
        Dim da As New OleDbDataAdapter(sql, cn)
        da.Fill(ds, dgrTitles.CurrentPageIndex * dgrTitles.PageSize, dgrTitles.PageSize, "Titles")
        ' Close the connection and release resources.
        cn.Close()

        ' Do the binding.
        dgrTitles.DataSource = ds.Tables("Titles")
        dgrTitles.DataBind()
    End Sub

#End If

    ' this event fires when a column header hyperlink is clicked

    Private Sub dgrTitles_SortCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridSortCommandEventArgs) Handles dgrTitles.SortCommand
        ' Extract current sort column and order.
        Dim currSortExpr As String = dgrTitles.Attributes("SortExpr")
        Dim newSortExpr As String = e.SortExpression

        ' If the sort expression is the same as the current one, just reverse the direction.
        If Not (currSortExpr Is Nothing) AndAlso currSortExpr.ToString = e.SortExpression Then
            newSortExpr &= " DESC"
        End If

        ' Remember the new sort expression and show the first page.
        dgrTitles.Attributes("SortExpr") = newSortExpr
        ShowPage(0)
    End Sub
End Class